using System;
using System.Data;
using System.Data.OleDb;
using LdExpress;
using System.IO;
using System.Web;
using MySql.Data.MySqlClient;
namespace LdExpress
{
    /// <summary>
    /// Pricing For Sales
    /// </summary>
    public class PricingOceanForSales
    {
        public long ID = 0;
        public string GoodsCode = "";
        public string GoodsNote = "";
        public long PolID = 0;
        public long PodID = 0;
        public string ContRequest1 = "";
        public string ContRequest2 = "";
        public string VolRequest1 = "";
        public string VolRequest2 = "";
        public string Payload1 = "";
        public string Payload2 = "";
        public string Termrs = "";
        public string Mode = "";
        public string VolM = "";
        public DateTime ETD = DateTime.MinValue;
        public string ETDAbout = "";
        public string ETDAboutEx = "";
        public DateTime ETA = DateTime.MinValue;
        public string ETAAbout = "";
        public string ETAAboutEx = "";
        public string TT_Request = "";
        public string SerType = "";
        public string Dlvry = "";
        public long CustRelID = 0;
        public string Note_Request = "";
        public DateTime UpdateTimeRequest = DateTime.Now;
        public long UserIDRequest = 0;
        public long UserIDResponse = 0;
        public string DC20 = "";
        public string DC40 = "";
        public string HC40 = "";
        public string RF20 = "";
        public string RF40 = "";
        public long CarrID = 0;
        public string FRE = "";
        public string TT_Response = "";
        public int Suare = 0;
        public string Note_Response = "";
        public int Bkg = 0;
        public int Targeted = 0;
        public string Idea = "";

        //Extention Field
        public string GoodsName = "";
        public string PolName = "";
        public string PodName = "";
        public string UserRequest = "";
        public string UserResponse = "";
        public string CustRelName = "";
        public string CarrName = "";

        public PricingOceanForSales()
        {
        }
        public PricingOceanForSales(long lID)
        {
            this.ID = lID;
        }
        public void Save(MySqlConnection con, MySqlTransaction trans)
        {
            string sqlCommand = "";
            if (ID <= 0) // new object, we insert new record to database
            {
                // build SQL statement
                sqlCommand = "Insert into tbl_PricingOceanForSales (" +
                             "GoodsCode," +
                             "GoodsNote," +
                             "PolID," +
                             "PodID," +
                             "ContRequest1," +
                             "ContRequest2," +
                             "VolRequest1," +
                             "VolRequest2," +
                             "Payload1," +
                             "Payload2," +
                             "Termrs," +
                             "Mode," +
                             "VolM," +
                             "ETD," +
                             "ETDAbout," +
                             "ETDAboutEx," +
                             "ETA," +
                             "ETAAbout," +
                             "ETAAboutEx," +
                             "TT_Request," +
                             "SerType," +
                             "Dlvry," +
                             "CustRelID," +
                             "Note_Request," +
                             "UpdateTimeRequest," +
                             "UserIDRequest," +
                             "UserIDResponse," +
                             "DC20," +
                             "DC40," +
                             "HC40," +
                             "RF20," +
                             "RF40," +
                             "CarrID," +
                             "FRE," +
                             "TT_Response," +
                             "Suare," +
                             "Note_Response," +
                             "Bkg," +
                             "Targeted," +
                             "Idea) " +
                         " values(" +
                             "'" + DBModule.RefineString(GoodsCode) + "'," +
                             "'" + DBModule.RefineString(GoodsNote) + "'," +
                             PolID.ToString() + "," +
                             PodID.ToString() + "," +
                             "'" + DBModule.RefineString(ContRequest1) + "'," +
                             "'" + DBModule.RefineString(ContRequest2) + "'," +
                             "'" + DBModule.RefineString(VolRequest1) + "'," +
                             "'" + DBModule.RefineString(VolRequest2) + "'," +
                             "'" + DBModule.RefineString(Payload1) + "'," +
                             "'" + DBModule.RefineString(Payload2) + "'," +
                             "'" + DBModule.RefineString(Termrs) + "'," +
                             "'" + DBModule.RefineString(Mode) + "'," +
                             "'" + DBModule.RefineString(VolM) + "'," +
                             DBModule.RefineDatetime(ETD, true) + "," +
                             "'" + DBModule.RefineString(ETDAbout) + "'," +
                             "'" + DBModule.RefineString(ETDAboutEx) + "'," +
                             DBModule.RefineDatetime(ETA, true) + "," +
                             "'" + DBModule.RefineString(ETAAbout) + "'," +
                             "'" + DBModule.RefineString(ETAAboutEx) + "'," +
                             "'" + DBModule.RefineString(TT_Request) + "'," +
                             "'" + DBModule.RefineString(SerType) + "'," +
                             "'" + DBModule.RefineString(Dlvry) + "'," +
                             CustRelID.ToString() + "," +
                             "'" + DBModule.RefineString(Note_Request) + "'," +
                             DBModule.RefineDatetime(UpdateTimeRequest, true) + "," +
                             UserIDRequest.ToString() + "," +
                             UserIDResponse.ToString() + "," +
                             "'" + DBModule.RefineString(DC20) + "'," +
                             "'" + DBModule.RefineString(DC40) + "'," +
                             "'" + DBModule.RefineString(HC40) + "'," +
                             "'" + DBModule.RefineString(RF20) + "'," +
                             "'" + DBModule.RefineString(RF40) + "'," +
                             CarrID.ToString() + "," +
                             "'" + DBModule.RefineString(FRE) + "'," +
                             "'" + DBModule.RefineString(TT_Response) + "'," +
                             Suare.ToString() + "," +
                             "'" + DBModule.RefineString(Note_Response) + "'," +
                             Bkg.ToString() + "," +
                             Targeted.ToString() + "," +
                             "'" + DBModule.RefineString(Idea) + "')";
                // run SQL statement
                DBModule.ExecuteNonQuery(sqlCommand, con, trans);
                UserObjectsLog.SaveObjectActionLog("PricingOceanForSales", "add", con, trans);
            }
            else // edit object, we update old record in database
            {
                // build SQL statement
                sqlCommand = "Update tbl_PricingOceanForSales set " +
                             "GoodsCode='" + DBModule.RefineString(GoodsCode) + "'," +
                             "GoodsNote='" + DBModule.RefineString(GoodsNote) + "'," +
                             "PolID=" + PolID.ToString() + "," +
                             "PodID=" + PodID.ToString() + "," +
                             "ContRequest1='" + DBModule.RefineString(ContRequest1) + "'," +
                             "ContRequest2='" + DBModule.RefineString(ContRequest2) + "'," +
                             "VolRequest1='" + DBModule.RefineString(VolRequest1) + "'," +
                             "VolRequest2='" + DBModule.RefineString(VolRequest2) + "'," +
                             "Payload1='" + DBModule.RefineString(Payload1) + "'," +
                             "Payload2='" + DBModule.RefineString(Payload2) + "'," +
                             "Termrs='" + DBModule.RefineString(Termrs) + "'," +
                             "Mode='" + DBModule.RefineString(Mode) + "'," +
                             "VolM='" + DBModule.RefineString(VolM) + "'," +
                             "ETD=" + DBModule.RefineDatetime(ETD, true) + "," +
                             "ETDAbout='" + DBModule.RefineString(ETDAbout) + "'," +
                             "ETDAboutEx='" + DBModule.RefineString(ETDAboutEx) + "'," +
                             "ETA=" + DBModule.RefineDatetime(ETA, true) + "," +
                             "ETAAbout='" + DBModule.RefineString(ETAAbout) + "'," +
                             "ETAAboutEx='" + DBModule.RefineString(ETAAboutEx) + "'," +
                             "TT_Request='" + DBModule.RefineString(TT_Request) + "'," +
                             "SerType='" + DBModule.RefineString(SerType) + "'," +
                             "Dlvry='" + DBModule.RefineString(Dlvry) + "'," +
                             "CustRelID=" + CustRelID.ToString() + "," +
                             "Note_Request='" + DBModule.RefineString(Note_Request) + "'," +
                             "UpdateTimeRequest=" + DBModule.RefineDatetime(UpdateTimeRequest, true) + "," +
                             "UserIDRequest=" + UserIDRequest.ToString() + "," +
                             "UserIDResponse=" + UserIDResponse.ToString() + "," +
                             "DC20='" + DBModule.RefineString(DC20) + "'," +
                             "DC40='" + DBModule.RefineString(DC40) + "'," +
                             "HC40='" + DBModule.RefineString(HC40) + "'," +
                             "RF20='" + DBModule.RefineString(RF20) + "'," +
                             "RF40='" + DBModule.RefineString(RF40) + "'," +
                             "CarrID=" + CarrID.ToString() + "," +
                             "FRE='" + DBModule.RefineString(FRE) + "'," +
                             "TT_Response='" + DBModule.RefineString(TT_Response) + "'," +
                             "Suare=" + Suare.ToString() + "," +
                             "Note_Response='" + DBModule.RefineString(Note_Response) + "'," +
                             "Bkg=" + Bkg.ToString() + "," +
                             "Targeted=" + Targeted.ToString() + "," +
                             "Idea='" + DBModule.RefineString(Idea) + "'" +
                    " where ID=" + ID.ToString();
                // run SQL statement
                DBModule.ExecuteNonQuery(sqlCommand, con, trans);
                UserObjectsLog.SaveObjectActionLog("PricingOceanForSales", "edit", con, trans);
            }
        }
        public void Delete(MySqlConnection con, MySqlTransaction trans)
        {
            //Buil SQL Statement
            //Run SQL Statement
            string sqlCommand = "DELETE FROM tbl_PricingOceanForSales WHERE ID = " + this.ID.ToString() + " limit 1";
            DBModule.ExecuteNonQuery(sqlCommand, con, trans);
            UserObjectsLog.SaveObjectActionLog("PricingOceanForSales", "delete", con, trans);
        }
        public void Load(MySqlConnection con, MySqlTransaction trans)
        {
            string sqlCommand = "";
            //Build SQL Statement
            //Run SQL Statement
            sqlCommand = "SELECT tbl_PricingOceanForSales.*" +
                ", GoodsTable.Name as GoodsName, PolTable.Name as PolName, PodTable.Name as PodName" +
                ",UserTableRequest.UserName as UserRequest ,UserTableResponse.UserName as UserResponse " +
                ",CustRelTable.Name as CustRelName ,CarrsTable.Name as CarrName " +
                " FROM tbl_PricingOceanForSales " +
                " Left join tbl_Goods GoodsTable on tbl_PricingOceanForSales.GoodsCode = GoodsTable.Code " +
                " Left join tbl_Ports PolTable on tbl_PricingOceanForSales.PolID = PolTable.ID " +
                " Left join tbl_Ports PodTable on tbl_PricingOceanForSales.PodID = PodTable.ID " +
                " Left join tbl_AdminUsers UserTableRequest on tbl_PricingOceanForSales.UserIDRequest = UserTableRequest.ID " +
                " Left join tbl_AdminUsers UserTableResponse on tbl_PricingOceanForSales.UserIDResponse = UserTableResponse.ID " +
                " Left join tbl_customerrelation CustRelTable on tbl_PricingOceanForSales.CustRelID = CustRelTable.ID " +
                " Left join tbl_Carrs CarrsTable on tbl_PricingOceanForSales.CarrID = CarrsTable.ID " +
                " WHERE tbl_PricingOceanForSales.ID = " + ID.ToString() + " limit 0,1";
            DataSet ds = DBModule.ExecuteQuery(sqlCommand, con, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID"))
                    ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("GoodsCode"))
                    GoodsCode = dr["GoodsCode"].ToString();
                if (!dr.IsNull("GoodsNote"))
                    GoodsNote = dr["GoodsNote"].ToString();
                if (!dr.IsNull("PolID"))
                    PolID = long.Parse(dr["PolID"].ToString());
                if (!dr.IsNull("PodID"))
                    PodID = long.Parse(dr["PodID"].ToString());
                if (!dr.IsNull("ContRequest1"))
                    ContRequest1 = dr["ContRequest1"].ToString();
                if (!dr.IsNull("ContRequest2"))
                    ContRequest2 = dr["ContRequest2"].ToString();
                if (!dr.IsNull("VolRequest1"))
                    VolRequest1 = dr["VolRequest1"].ToString();
                if (!dr.IsNull("VolRequest2"))
                    VolRequest2 = dr["VolRequest2"].ToString();
                if (!dr.IsNull("Payload1"))
                    Payload1 = dr["Payload1"].ToString();
                if (!dr.IsNull("Payload2"))
                    Payload2 = dr["Payload2"].ToString();
                if (!dr.IsNull("Termrs"))
                    Termrs = dr["Termrs"].ToString();
                if (!dr.IsNull("Mode"))
                    Mode = dr["Mode"].ToString();
                if (!dr.IsNull("VolM"))
                    VolM = dr["VolM"].ToString();
                if (!dr.IsNull("ETD"))
                    ETD = DateTime.Parse(dr["ETD"].ToString());
                if (!dr.IsNull("ETDAbout"))
                    ETDAbout = dr["ETDAbout"].ToString();
                if (!dr.IsNull("ETDAboutEx"))
                    ETDAboutEx = dr["ETDAboutEx"].ToString();
                if (!dr.IsNull("ETA"))
                    ETA = DateTime.Parse(dr["ETA"].ToString());
                if (!dr.IsNull("ETAAbout"))
                    ETAAbout = dr["ETAAbout"].ToString();
                if (!dr.IsNull("ETAAboutEx"))
                    ETAAboutEx = dr["ETAAboutEx"].ToString();
                if (!dr.IsNull("TT_Request"))
                    TT_Request = dr["TT_Request"].ToString();
                if (!dr.IsNull("SerType"))
                    SerType = dr["SerType"].ToString();
                if (!dr.IsNull("Dlvry"))
                    Dlvry = dr["Dlvry"].ToString();
                if (!dr.IsNull("CustRelID"))
                    CustRelID = long.Parse(dr["CustRelID"].ToString());
                if (!dr.IsNull("Note_Request"))
                    Note_Request = dr["Note_Request"].ToString();
                if (!dr.IsNull("UpdateTimeRequest"))
                    UpdateTimeRequest = DateTime.Parse(dr["UpdateTimeRequest"].ToString());
                if (!dr.IsNull("UserIDRequest"))
                    UserIDRequest = long.Parse(dr["UserIDRequest"].ToString());
                if (!dr.IsNull("UserIDResponse"))
                    UserIDResponse = long.Parse(dr["UserIDResponse"].ToString());
                if (!dr.IsNull("DC20"))
                    DC20 = dr["DC20"].ToString();
                if (!dr.IsNull("DC40"))
                    DC40 = dr["DC40"].ToString();
                if (!dr.IsNull("HC40"))
                    HC40 = dr["HC40"].ToString();
                if (!dr.IsNull("RF20"))
                    RF20 = dr["RF20"].ToString();
                if (!dr.IsNull("RF40"))
                    RF40 = dr["RF40"].ToString();
                if (!dr.IsNull("CarrID"))
                    CarrID = long.Parse(dr["CarrID"].ToString());
                if (!dr.IsNull("FRE"))
                    FRE = dr["FRE"].ToString();
                if (!dr.IsNull("TT_Response"))
                    TT_Response = dr["TT_Response"].ToString();
                if (!dr.IsNull("Suare"))
                    Suare = int.Parse(dr["Suare"].ToString());
                if (!dr.IsNull("Note_Response"))
                    Note_Response = dr["Note_Response"].ToString();
                if (!dr.IsNull("Bkg"))
                    Bkg = int.Parse(dr["Bkg"].ToString());
                if (!dr.IsNull("Targeted"))
                    Targeted = int.Parse(dr["Targeted"].ToString());
                if (!dr.IsNull("Idea"))
                    Idea = dr["Idea"].ToString();

                if (!dr.IsNull("GoodsName"))
                    GoodsName = dr["GoodsName"].ToString();
                if (!dr.IsNull("PolName"))
                    PolName = dr["PolName"].ToString();
                if (!dr.IsNull("PodName"))
                    PodName = dr["PodName"].ToString();
                if (!dr.IsNull("UserRequest"))
                    UserRequest = dr["UserRequest"].ToString();
                if (!dr.IsNull("UserResponse"))
                    UserResponse = dr["UserResponse"].ToString();
                if (!dr.IsNull("CustRelName"))
                    CustRelName = dr["CustRelName"].ToString();
                if (!dr.IsNull("CarrName"))
                    CarrName = dr["CarrName"].ToString();
            }
            else
            {
            }
        }
        public static void GetList(string Condition, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection con, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string sqlCommand = "";
            ds = null;
            // build SQL statement
            sqlCommand = "SELECT tbl_PricingOceanForSales.*" +
                 " ,IF(tbl_pricingoceanforsales.Dc20='' AND tbl_pricingoceanforsales.DC40='' AND tbl_pricingoceanforsales.HC40=''" +
                 " AND tbl_pricingoceanforsales.RF20='' AND tbl_pricingoceanforsales.RF40='',0,1) AS KeyTTIsInput" +
                 " ,TIMESTAMPDIFF(HOUR,UpdateTimeRequest,NOW()) AS DiffHours" +
                 ", GoodsTable.Name as GoodsName, PolTable.Name as PolName, PodTable.Name as PodName" +
                 ",UserTableRequest.UserName as UserRequest ,UserTableResponse.UserName as UserResponse " +
                 ",CustRelTable.Name as CustRelName,CarrsTable.Name as CarrName " +
                 " FROM tbl_PricingOceanForSales " +
                 " Left join tbl_Goods GoodsTable on tbl_PricingOceanForSales.GoodsCode = GoodsTable.Code " +
                 " Left join tbl_Ports PolTable on tbl_PricingOceanForSales.PolID = PolTable.ID " +
                 " Left join tbl_Ports PodTable on tbl_PricingOceanForSales.PodID = PodTable.ID " +
                 " Left join tbl_AdminUsers UserTableRequest on tbl_PricingOceanForSales.UserIDRequest = UserTableRequest.ID " +
                 " Left join tbl_AdminUsers UserTableResponse on tbl_PricingOceanForSales.UserIDResponse = UserTableResponse.ID " +
                 " Left join tbl_customerrelation CustRelTable on tbl_PricingOceanForSales.CustRelID = CustRelTable.ID " +
                 " Left join tbl_Carrs CarrsTable on tbl_PricingOceanForSales.CarrID = CarrsTable.ID " +
                 " WHERE 1=1";
            if ((Condition != "") && (Condition != null))
            {
                sqlCommand += Condition;
            }

            if ((OrderBy != null) && (OrderBy != ""))
                sqlCommand = sqlCommand + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(sqlCommand, NumOfItemsPerPage, PageNo, out NumberOfPages, con, trans);
            }
            else
                ds = DBModule.ExecuteQuery(sqlCommand, con, trans);
        }
        public static void DoSearch(string condition, string OrderBy, out DataSet ds, MySqlConnection con, MySqlTransaction trans)
        {
            string sqlCommand = "";
            ds = null;
            // build SQL statement
            sqlCommand = "Select `ID` from tbl_PricingOceanForSales where 1=1 ";
            if (condition.Length > 0)
                sqlCommand += condition;
            if ((OrderBy != null) && (OrderBy != ""))
                sqlCommand = sqlCommand + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            ds = DBModule.ExecuteQuery(sqlCommand, con, trans);
        }
    }
}
